--SELECT * FROM lion_candidate
--DELETE FROM lion_candidate
--INSERT INTO lion_candidate
SELECT 
 case len(convert(varchar(10),rs.id))
	when 7 then '00000000-0000-0000-0000-00000' + convert(varchar(10),rs.id)
    when 6 then '00000000-0000-0000-0000-000000' + convert(varchar(10),rs.id)
    when 5 then '00000000-0000-0000-0000-0000000' + convert(varchar(10),rs.id) 
    when 4 then '00000000-0000-0000-0000-00000000' + convert(varchar(10),rs.id) 
    when 3 then '00000000-0000-0000-0000-000000000' + convert(varchar(10),rs.id) 
    when 2 then '00000000-0000-0000-0000-0000000000' + convert(varchar(10),rs.id) 
    when 1 then '00000000-0000-0000-0000-00000000000'+ convert(varchar(10),rs.id) 
    end  AS [Id],NULL AS [FolderId],isnull(r.FirstNameCn,'') AS [FullName],r.[Sex] AS [Gender],r.Birthday AS [BirthDate],r.Education AS [EducationLevel],r.WorkYear AS [YearsOfExperience],NULL AS [CareerLevel],r.updateddate AS [ResumeUpdated],case len(convert(varchar(10),rs.jobid))
    when 5 then '00000000-0000-0000-0000-0000000' + convert(varchar(10),rs.jobid) 
    when 6 then '00000000-0000-0000-0000-000000' + convert(varchar(10),rs.jobid) end  AS [JobId],case len(convert(varchar(10),rs.jobid))
    when 5 then '00000000-0000-0000-0000-0000000' + convert(varchar(10),rs.jobid) 
    when 6 then '00000000-0000-0000-0000-000000' + convert(varchar(10),rs.jobid) end  AS [JobPostingId],j.jobname AS [JobTitle],NULL AS [DesiredSalary],joblettercontent AS [CoverLetter],'00000000-0000-0000-0000-00000' + convert(varchar(7),resumeid) AS  [ResumeId],rs.createddate AS [DateApplied],rs.updateddate AS [DateReceived],NULL AS [Rating],dbo.getscore(rs.companyid,rs.resumeid) AS [Notes],rs.repplyflag AS [Status],0 AS [IsAccepted],dbo.getdeleted(rs.companyid,rs.jobid,rs.resumeid) As [Deleted],--0 AS [Deleted],rs.updateddate AS [LastUpdated],rs.updateddate  AS [LastVisited],case len(convert(varchar(10),rs.companyid))
    when 5 then '00000000-0000-0000-0000-0000000' + convert(varchar(10),rs.companyid) 
    when 4 then '00000000-0000-0000-0000-00000000' + convert(varchar(10),rs.companyid) 
    when 3 then '00000000-0000-0000-0000-000000000' + convert(varchar(10),rs.companyid) 
    when 2 then '00000000-0000-0000-0000-0000000000' + convert(varchar(10),rs.companyid) 
    when 1 then '00000000-0000-0000-0000-00000000000'+ convert(varchar(10),rs.companyid) 
    end AS [CompanyId],case len(convert(varchar(10),rs.companyid))
    when 5 then '00000000-0000-0000-0000-0000000' + convert(varchar(10),rs.companyid) 
    when 4 then '00000000-0000-0000-0000-00000000' + convert(varchar(10),rs.companyid) 
    when 3 then '00000000-0000-0000-0000-000000000' + convert(varchar(10),rs.companyid) 
    when 2 then '00000000-0000-0000-0000-0000000000' + convert(varchar(10),rs.companyid) 
    when 1 then '00000000-0000-0000-0000-00000000000'+ convert(varchar(10),rs.companyid) 
    end AS [ModifiedBy]FROM srv_lnk.nchr2008.dbo.ReciveResume rs left join srv_lnk.nchr2008.dbo.companyjob j
on rs.jobid = j.id left join srv_lnk.nchr2008.dbo.personresume r
on rs.resumeid = r.id
where rs.companyid in(select id from companyinfo) and rs.createddate >= '2012-5-1'

